PostgreSQL 高可用 3节点流复制

1 配置DNS解析

所有节点执行如下操作：

cat >> /etc/hosts << EOF 
192.168.10.191 vip 
192.168.10.192 node1
192.168.10.193 node2
192.168.10.194 node3
EOF

2 node1 节点上的操作

2.1 创建归档目录

su - root 
mkdir -p /archive
chown postgres:postgres /archive

2.2 配置归档及流复制参数

cat >> $PGDATA/postgresql.conf <<EOF
archive_mode = on
archive_command='test ! -f /archive/%f && cp %p /archive/%f'
wal_log_hints=on
max_wal_senders = 10            # max number of walsender processes
max_replication_slots = 10
#wal_keep_segments = 1024	# in logfile segments, 16MB each; 0 disables 
hot_standby = on
synchronous_commit = on
synchronous_standby_names = '1(node1,node2,node3)'
hot_standby_feedback=on
fsync=on
EOF
echo ''

2.3 配置sys_hba.conf

vi $PGDATA/pg_hba.conf
host    replication     all             192.168.10.0/24         scram-sha-256

2.4 创建流复制用户

psql -U postgres -d testdb 
CREATE USER repl WITH PASSWORD 'repl' REPLICATION;

2.5 重启数据库

pg_ctl restart -D $PGDATA

2.6 创建复制槽

psql -U postgres -d testdb 
SELECT * FROM pg_create_physical_replication_slot('slot_node2');

SELECT * FROM pg_create_physical_replication_slot('slot_node3');

SELECT slot_name, slot_type, active FROM pg_replication_slots;
//屏幕输出：
 slot_name  | slot_type | active
------------+-----------+--------
 slot_node2 | physical  | f
 slot_node3 | physical  | f

3 node2 节点上的操作

3.1 清空数据集簇目录

pg_ctl stop -D $PGDATA 
rm -rf $PGDATA/*
ll $PGDATA/

3.2 传输全量数据

pg_basebackup -h node1 -p 5432 -U repl -Fp -X stream -v -P  -D $PGDATA
//屏幕输出：
Password: repl
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2737"
42240/42240 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

3.3 配置kingbase.conf 、standby.signal 文件

cat >> $PGDATA/postgresql.conf << EOF
primary_conninfo = 'user=repl password=repl host=node1 port=5432 application_name=node2'
recovery_target_timeline='latest'
primary_slot_name ='slot_node2'
EOF

touch $PGDATA/standby.signal

3.4 启动node2 节点的服务

pg_ctl start -D $PGDATA

3.5 验证receiver 进程是否启动。

ps -ef|grep -v grep|grep -E 'sender|receiver'

如果receiver 进程未启动，请查看数据库日志。

4 node3 节点上的操作

4.1 清空数据集簇目录

pg_ctl stop -D $PGDATA 
rm -rf $PGDATA/*
ll $PGDATA/

4.2 传输全量数据

pg_basebackup -h node1 -p 5432 -U repl -Fp -X stream -v -P  -D $PGDATA
//屏幕输出：
Password:repl
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2737"
42240/42240 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/3000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

4.3 配置kingbase.conf 、standby.signal 文件

cat >> $PGDATA/postgresql.conf << EOF
primary_conninfo = 'user=repl password=repl host=node1 port=5432 application_name=node3'
recovery_target_timeline='latest'
primary_slot_name ='slot_node3'
EOF

touch $PGDATA/standby.signal

4.4 启动node2 节点的服务

pg_ctl start -D $PGDATA

4.5 验证receiver 进程是否启动。

ps -ef|grep -v grep|grep -E 'sender|receiver'

如果receiver 进程未启动，请查看数据日志。

5 流复制验证

psql -U postgres -d testdb 
CREATE TABLE t01(id int);

在主节点执行，备节点查看。

6 流复制健康状态检查

6.1 查看数据库状态

1、查看状态

select pg_is_in_recovery();

2、查看进程

ps -ef|grep -v grep|grep -E 'sender|receiver'

3、查看控制文件

pg_controldata -D $PGDATA | grep -E '状态|state'

4、查看 standby.single 文件

find $PGDATA -name standby.signal

6.2 查看流复制状态

\x
SELECT * FROM pg_stat_replication;

7 三节点主备切换

7.1 node 1 准备

1、确认主库相关信息

postgres> psql -U postgres -d testdb
pg_controldata -D /data | grep -E '状态|state'
find /data -name standby.signal
ps -ef | grep -v grep | grep -E 'sender|receiver'
ksql -Usystem -dtest
\x
SELECT * FROM pg_stat_replication;
SELECT pg_is_in_recovery();

1、关闭node1

pg_ctl stop -D $PGDATA

2、主节点停机后备节点信息

cd $PGDATA/pg_log
tail -n5  `ls -lrt $PGDATA/pg_log|tail -1|awk '{print $NF}'`

7.2 node2成为主节点

1、备变主

pg_ctl promote -D $PGDATA

2、查看Node2 信息

pg_controldata -D $PGDATA |grep -E '状态|state'
find $PGDATA -name standby.signal
ps -ef|grep -v grep|grep -E 'sender|receiver'

3、创建复制槽

psql -h node2 -p 1922 -U repl -d testdb
SELECT slot_name, slot_type, active FROM pg_replication_slots;  
SELECT * FROM pg_create_physical_replication_slot('slot_node1');
SELECT * FROM pg_create_physical_replication_slot('slot_node3');
SELECT slot_name, slot_type, active FROM pg_replication_slots; 
\! ps -ef|grep -v grep|grep -E 'sender|receiver'

7.3 node1 成为备库

1、创建standby.signal

touch $PGDATA/standby.signal

2、配置参数

cat >> $PGDATA/postgresql.auto.conf << EOF
primary_conninfo = 'user=repl passfile=''/home/postgres/.pgpass'' host=node2 port=1922 application_name=node1 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'slot_node1'
EOF

3、启动该节点

$ pg_ctl start -D $PGDATA

4、主变备后信息

pg_controldata -D $PGDATA |grep -E '状态|state'
find $PGDATA -name standby.signal
ps -ef|grep -v grep|grep -E 'sender|receiver'

7.4 node3 流复制重定向

1、停止备库Node3

pg_ctl stop -D $PGDATA

2、配置参数

vi $PGDATA/postgresql.auto.conf
--------------------input------------------------------
primary_conninfo = 'user=repl passfile=''/home/postgres/.pgpass'' host=node2 port=1922 application_name=node3 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'slot_node3'

3、启动该节点

pg_ctl start -D $PGDATA

4、主变备后信息

pg_controldata -D $PGDATA |grep -E '状态|state'
find $PGDATA -name standby.signal
ps -ef|grep -v grep|grep -E 'sender|receiver'

7.5 验证同步

创建一张表，验证其他数据库是否同步。

CREATE TABLE t01(id int);

8 延迟复制

postgres> psql -U repl -d testdb
ALTER SYSTEM SET recovery_min_apply_delay=120000;

8.1 查看复制槽状态

SELECT * FROM pg_replication_slots;